using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using NCS.Module.CMS.Library.Definitions;
using NCS.Library;
using NCS.Library.Utilities;
using NCS.Library.Components;

namespace NCS.Module.CMS.Library.Comments
{
    public class CommentDB
    {
        public static string key = "Cache_Comment";
        private static double GetExpire()
        {
            return Constants.CacheExpire_Content;
        }

        public static DataTable GetByContentID(int contentID, bool getByAccept)
        {
            DataTable retVal = null;
            DataCaching dc = new DataCaching();
            string param = contentID + "_" + (getByAccept == true ? "1" : "0");
            if (getByAccept)
            {                   
                retVal = (DataTable)dc.GetHashCache(key, param);
            }
            if (retVal == null)
            {
                SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
                SqlCommand dbCmd = new SqlCommand("CMS_Comments_GetByContentID", dbConn);
                dbCmd.Parameters.AddWithValue("@Content_ID", contentID);
                dbCmd.Parameters.AddWithValue("@Comment_Accept", getByAccept);
                dbCmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    dbConn.Open();
                    retVal = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                    da.Fill(retVal);
                }
                finally
                {
                    dbConn.Close();
                }
                dc.SetHashCache(key, param, 3, retVal);
            }
            return retVal;
        }

        public static DataTable GetByContentID(int contentID, bool getByAccept, int numberRecored)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("CMS_Comments_GetByContentIDandRecord", dbConn);
            dbCmd.Parameters.AddWithValue("@Comment_Accept", getByAccept);
            dbCmd.Parameters.AddWithValue("@Content_ID", contentID);
            dbCmd.Parameters.AddWithValue("@record", numberRecored);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable TopComments(int recordNumber)
        {
            DataTable retVal = null;
            DataCaching dc = new DataCaching();
            retVal = (DataTable)dc.GetHashCache(key, recordNumber+"_" + AppEnv.PortalId());
            if (retVal == null)
            {
                SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

                SqlCommand dbCmd = new SqlCommand("CMS_Comment_GetTopContent", dbConn);
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.AddWithValue("@Record", recordNumber);
                dbCmd.Parameters.AddWithValue("@PortalID", ConvertUtility.ToInt32(AppEnv.PortalId()));
                try
                {
                    dbConn.Open();
                    retVal = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                    da.Fill(retVal);
                }
                finally
                {
                    dbConn.Close();
                }
                dc.SetHashCache(key, recordNumber + "_" + AppEnv.PortalId(), 1, retVal);
            }            
            return retVal;
        }
        public static DataTable TopComments(int recordNumber, int portalid)
        {
            DataTable retVal = null;
            DataCaching dc = new DataCaching();
            retVal = (DataTable)dc.GetHashCache(key, recordNumber + "_" + portalid.ToString());
            if (retVal == null)
            {
                SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

                SqlCommand dbCmd = new SqlCommand("CMS_Comment_GetTopContent", dbConn);
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.AddWithValue("@Record", recordNumber);
                dbCmd.Parameters.AddWithValue("@PortalID", portalid);
                try
                {
                    dbConn.Open();
                    retVal = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                    da.Fill(retVal);
                }
                finally
                {
                    dbConn.Close();
                }
                dc.SetHashCache(key, recordNumber + "_" + AppEnv.PortalId(), 1, retVal);
            }
            return retVal;
        }
        public static DataTable TopComments_week(int recordNumber)
        {
            DataTable retVal = null;
            DataCaching dc = new DataCaching();
            retVal = (DataTable)dc.GetHashCache(key, recordNumber);
            if (retVal == null)
            {
                SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
                SqlCommand dbCmd = new SqlCommand("CMS_Comment_GetTopContentInWeek", dbConn);
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.AddWithValue("@Record", recordNumber);
                try
                {
                    dbConn.Open();
                    retVal = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                    da.Fill(retVal);
                }
                finally
                {
                    dbConn.Close();
                }
                dc.SetHashCache(key, recordNumber, 1, retVal);
            }     
            return retVal;
        }

        public static DataTable TopComments_all(int recordNumber)
        {
            DataTable retVal = null;
            DataCaching dc = new DataCaching();
            retVal = (DataTable)dc.GetHashCache(key, "all" + recordNumber);
            if (retVal == null)
            {
                SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

                SqlCommand dbCmd = new SqlCommand("CMS_Comment_GetTopAllContent", dbConn);
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.AddWithValue("@Record", recordNumber);
                try
                {
                    dbConn.Open();
                    retVal = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                    da.Fill(retVal);
                }
                finally
                {
                    dbConn.Close();
                }
                dc.SetHashCache(key, "all" + recordNumber, 1, retVal);
            }
            return retVal;
        }

        public static int NumOfCommentByUserAndPortal(int userid, int portalid)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("CMS_Comment_GetNumOfComment", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@UserID", userid);
            dbCmd.Parameters.AddWithValue("@PortalID", portalid);
            dbCmd.Parameters.AddWithValue("@TotalRecord", SqlDbType.Int).Direction = ParameterDirection.Output;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
                retVal = ConvertUtility.ToInt32(dbCmd.Parameters["@TotalRecord"].Value);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        
        public static DataTable GetUnRead(int numberRecored)
        {
            DataTable retVal = null;

            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            string strSQL = "SELECT TOP " + numberRecored;
            strSQL += " * FROM CMS_Comments WHERE Comment_Read = 0 ";
            strSQL += " ORDER BY Comment_DateTime DESC ";
            SqlCommand dbCmd = new SqlCommand(strSQL, dbConn);
            dbCmd.CommandType = CommandType.Text;
            try
            {
                dbConn.Open();
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable GetAll()
        {
            DataTable retVal = null;
            //DataCaching dc = new DataCaching();
            //retVal = (DataTable)dc.GetHashCache(key, "Getall");
            //if (retVal == null)
            //{
                SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
                SqlCommand dbCmd = new SqlCommand("CMS_Comments_GetAllbyCMS", dbConn);
                
                dbCmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    dbConn.Open();
                    retVal = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                    da.Fill(retVal);
                }
                finally
                {
                    dbConn.Close();
                }
                //dc.SetHashCache(key, "Getall", 1, retVal);
            //}
            return retVal;
        }
        public static DataTable GetAllByUserAndPortalID(int zoneid, int userid, string text, string lang,int portalid, DateTime fromdate, DateTime todate, string rank)
        {
            DataTable retVal = null;
            //DataCaching dc = new DataCaching();
            //retVal = (DataTable)dc.GetHashCache(key, "CMS_Comments_GetAllByUserIDAndPortalID");
            //if (retVal == null)
            //{  
                SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
                SqlCommand dbCmd = new SqlCommand("CMS_Comments_GetAllByUserIDAndPortalID", dbConn);
                dbCmd.Parameters.AddWithValue("@ZoneID", zoneid);
                dbCmd.Parameters.AddWithValue("@UserID", userid);
                dbCmd.Parameters.AddWithValue("@Text", text);
                dbCmd.Parameters.AddWithValue("@Lang", lang);
                dbCmd.Parameters.AddWithValue("@PortalID", portalid);
                dbCmd.Parameters.AddWithValue("@FromDate", fromdate);
                dbCmd.Parameters.AddWithValue("@ToDate", todate);
                dbCmd.Parameters.AddWithValue("@Distribution_Rank", rank);
                dbCmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    dbConn.Open();
                    retVal = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                    da.Fill(retVal);
                }
                finally
                {
                    dbConn.Close();
                }
                //dc.SetHashCache(key, "Getall", 1, retVal);
            //}
            return retVal;
        }

        public static void Accept(int _comment_ID, bool accept, int userid)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("CMS_Comments_Accept", dbConn);
            dbCmd.Parameters.AddWithValue("@Comment_Accept", accept);
            dbCmd.Parameters.AddWithValue("@User_ID", userid);
            dbCmd.Parameters.AddWithValue("@Comment_ID", _comment_ID);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static void Accept1(int _comment_ID, bool accept)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("CMS_Comments_Accept1", dbConn);
            dbCmd.Parameters.AddWithValue("@Comment_Accept", accept);
            dbCmd.Parameters.AddWithValue("@Comment_ID", _comment_ID);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static void UpdateRead(int _comment_ID, bool accept)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("CMS_Comments_UpdateRead", dbConn);
            dbCmd.Parameters.AddWithValue("@Comment_Accept", accept);
            dbCmd.Parameters.AddWithValue("@Comment_ID", _comment_ID);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static void Delete(int _comment_ID)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("CMS_Comments_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Comment_ID", _comment_ID);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static int Insert(CommentInfo _commentInfo)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("CMS_Comments_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Content_ID", _commentInfo.Content_ID);
            dbCmd.Parameters.AddWithValue("@Comment_Name", _commentInfo.Comment_Name);
            dbCmd.Parameters.AddWithValue("@Comment_Email", _commentInfo.Comment_Email);
            dbCmd.Parameters.AddWithValue("@Comment_Address", _commentInfo.Comment_Address);
            dbCmd.Parameters.AddWithValue("@Comment_Content", SqlDbType.NText).Value = _commentInfo.Comment_Content;
            dbCmd.Parameters.AddWithValue("@Comment_Accept", _commentInfo.Comment_Accept);
            dbCmd.Parameters.AddWithValue("@Comment_Read", _commentInfo.Comment_Read);
            if(_commentInfo.Type_Comment > 0)
                dbCmd.Parameters.AddWithValue("@Type_Comment", _commentInfo.Type_Comment);
            else
                dbCmd.Parameters.AddWithValue("@Type_Comment", (int)Comment_Type.CMS);

            dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
                return (int)dbCmd.Parameters["@RETURN_VALUE"].Value;
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static void Update(CommentInfo _commentInfo)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("CMS_Comments_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Comment_ID", _commentInfo.Comment_ID);
            dbCmd.Parameters.AddWithValue("@Content_ID", _commentInfo.Content_ID);
            dbCmd.Parameters.AddWithValue("@Comment_Name", _commentInfo.Comment_Name);
            dbCmd.Parameters.AddWithValue("@Comment_Email", _commentInfo.Comment_Email);
            dbCmd.Parameters.AddWithValue("@Comment_Address", _commentInfo.Comment_Address);
            dbCmd.Parameters.AddWithValue("@Comment_Content", _commentInfo.Comment_Content);
            dbCmd.Parameters.AddWithValue("@Comment_Datetime", _commentInfo.Comment_Datetime);
            dbCmd.Parameters.AddWithValue("@Comment_Accept", _commentInfo.Comment_Accept);
            dbCmd.Parameters.AddWithValue("@Comment_Read", _commentInfo.Comment_Read);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static CommentInfo GetInfo(int _comment_ID)
        {
            CommentInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("CMS_Comments_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Comment_ID", _comment_ID);
            try
            {
                dbConn.Open();
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new CommentInfo();
                    retVal.Comment_ID = Convert.ToInt32(dr["Comment_ID"]);
                    retVal.Content_ID = Convert.ToInt32(dr["Content_ID"]);
                    retVal.Comment_Name = Convert.ToString(dr["Comment_Name"]);
                    retVal.Comment_Email = Convert.ToString(dr["Comment_Email"]);
                    retVal.Comment_Address = Convert.ToString(dr["Comment_Address"]);
                    retVal.Comment_Content = Convert.ToString(dr["Comment_Content"]);
                    retVal.Comment_Datetime = Convert.ToDateTime(dr["Comment_Datetime"]);
                    retVal.Comment_Accept = Convert.ToBoolean(dr["Comment_Accept"]);
                    retVal.Comment_Read = Convert.ToBoolean(dr["Comment_Read"]);
                    retVal.User_ID = ConvertUtility.ToInt32(dr["User_ID"]);
                    retVal.Type_Comment = ConvertUtility.ToInt32(dr["Type_Comment"]);
                }
                if (dr != null) dr.Close();
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
    }
}
